/*/{Protheus.doc} SYNCCTA
E a User Function de geracao do layout CTA (Contas Contabeis) do Sistema Synchro
@author Elton Teodoro Alves
@since 14/11/2014
@version P11 R5
@Param aParam,Array,Array enviado pelo schudele
@Example
Esta User Function foi desenvolvida para ser utilizada com o Schudele, que passa como parametro para User Function um array com a seguinte configuracao:</br>
</br>
<table>
<tr><th> POSICAO </th><th> TIPO </th><th> DESCRICAO             </th></tr>
<tr><td> [01]    </td><td> C    </td><td> Codigo da Empresa     </td></tr>
<tr><td> [02]    </td><td> C    </td><td> Codigo da Filial      </td></tr>
<tr><td> [03]    </td><td> C    </td><td> Codigo do Agendamento </td></tr>
<tr><td> [04]    </td><td> C    </td><td> Codigo da Tarefa      </td></tr>
</table>
</br>	
Para teste fora do schudele, pode-se enviar para o SmartClient com a clausula -a estes dados separadas por virgula ',' Ex.: 99,01,0001,0001
</br>
Para teste pode-se enviar para o smart client com a clausula -a com este dados separadas por UnderLine ',' Ex.: 99,01,0001,0001
/*/

#Define CRLF Chr(13) + Chr(10)
Static PERIOD := 'M'

#Include 'Protheus.ch'

User Function SYNCCTA(xParam)
	
	Local	cLayout		:=	Right(ProcName(),3)
	Local	aParam		:=	If(Valtype(xParam) == 'C',Separa(xParam,',',.T.),xParam)
	Local	aSyncEnv	:=	U_SYNCENV(aParam,cLayout,PERIOD)
	Local	cTrab		:=	GetNextAlias()
	Local	cTempTrab	:=	cLayout + Dtos(Date()) + StrTran(Time(),':','')
	Local	cTempFile	:=	GetSrvProfString("StartPath","") + cTempTrab + GetDbExtension()
	Local	cQuery		:=	''
	Local	cQueryBkp	:=	''
	Local	aStru		:=	{}
	Local	nX			:=	0
	Local	aFiliais	:=	StrTokArr(AllTrim(aSyncEnv[10]),'/',)
	Local	cFiliais	:=	''
	Local	nCount		:=	0
	Local	aEntPlan	:=	StrTokArr(AllTrim(GetMv('MV_SYNC003')),',',)
	Local	aLstCta		:=	{}
	Local	aCtRfSint	:=	''
	
	// Monta a string com a lista de filiais para ser utilizada na query
	
	For nX := 1 To Len(aFiliais)
		
		cFiliais += "'" + aFiliais[nX] + "'"
		
		If nX # Len(aFiliais)
			
			cFiliais += ","
			
		End If
		
	Next nX
	
	// --------------------------------------------------------------
	// Monta estrutura do arquivo temporario com dados a serem exportados
	// --------------------------------------------------------------
	aAdd(aStru,{'POS0001','C',0006,0}) // CHAVE_LOTE
	aAdd(aStru,{'POS0007','C',0010,0}) // ORIGEM
	aAdd(aStru,{'POS0017','C',0040,0}) // CHAVE_ORIGEM
	aAdd(aStru,{'POS0057','C',0001,0}) // CTRL_INSTRUCAO
	aAdd(aStru,{'POS0058','C',0018,0}) // EST_CODIGO
	aAdd(aStru,{'POS0076','C',0008,0}) // DT_INICIO
	aAdd(aStru,{'POS0084','C',0025,0}) // CODIGO CONTA
	aAdd(aStru,{'POS0109','C',0150,0}) // DESCRICAO
	aAdd(aStru,{'POS0259','C',0008,0}) // DT_FIM
	aAdd(aStru,{'POS0267','C',0001,0}) // IND_CMB
	aAdd(aStru,{'POS0268','C',0001,0}) // IND_IMOBILIZADO
	aAdd(aStru,{'POS0269','C',0001,0}) // IND_ULTIMO_NIVEL_CONTA
	aAdd(aStru,{'POS0270','C',0025,0}) // CONTA TOTALIZADORA
	aAdd(aStru,{'POS0295','C',0001,0}) // TIPO CONTA
	aAdd(aStru,{'POS0296','C',0001,0}) // NIVEL
	aAdd(aStru,{'POS0297','C',0025,0}) // COD_CTA_PAD
	
	// --------------------------------------------------------------
	
	If aSyncEnv[6]
		
		// --------------------------------------------------------------
		// Montagem da query de pesquisa
		// --------------------------------------------------------------
		cQuery += "SELECT " + CRLF
		
		cQuery += "CT1.CT1_NATCTA NATUREZA," + CRLF
		cQuery += "CT1.CT1_CTAVM CTAVM,"
		
		//------------------------------------------------
		
		cQuery += "'' POS0001," + CRLF
		cQuery += "'' POS0007," + CRLF
		cQuery += "CT1.CT1_CONTA POS0017," + CRLF
		cQuery += "'I' POS0057," + CRLF
		cQuery += "'' POS0058," + CRLF
		cQuery += "CT1.CT1_DTEXIS POS0076," + CRLF
		cQuery += "CT1.CT1_CONTA POS0084," + CRLF
		cQuery += "CT1.CT1_DESC01 POS0109," + CRLF
		cQuery += "'47121231' POS0259," + CRLF
		cQuery += "'' POS0267," + CRLF
		cQuery += "CT1.CT1_XATIMB POS0268," + CRLF
		cQuery += "'S' POS0269," + CRLF
		cQuery += "ISNULL((SELECT MIN(CVD.CVD_CTAREF) FROM " + RetSqlName("CVD") + " CVD WHERE CVD.CVD_CONTA = CT1.CT1_CONTA),'') POS0270," + CRLF
		cQuery += "CT1.CT1_NATCTA POS0295," + CRLF
		cQuery += "'' POS0296," + CRLF
		cQuery += "'' POS0297" + CRLF
		
		cQuery += "FROM " + RetSqlName("CT1") + " CT1 " + CRLF
		
		cQuery += "WHERE CT1.D_E_L_E_T_ <> '*' AND " + CRLF
		cQuery += "CT1.CT1_BLOQ = '2' AND " + CRLF
		cQuery += "CT1.CT1_CLASSE = '2' " + CRLF
		
		// --------------------------------------------------------------
		
		cQueryBkp	:=	cQuery
		
		cQuery := ChangeQuery(cQuery)
		
		If TcSqlExec(cQuery) < 0
			
			U_SYNCMAIL(AllTrim(aSyncEnv[15]),cLayout + ' - ' + AllTrim(aSyncEnv[17]),'Erro na execucao da query no banco de dados: ' + Chr(13) + Chr(10) + AllTrim(TcSqlError()),AllTrim(SubStr(aSyncEnv[4],1,6)),AllTrim(SubStr(aSyncEnv[4],7,6)),1,aSyncEnv[8],aSyncEnv[9])
			
		Else
			
			DbUseArea(.T.,"TOPCONN",TCGenQry(,,cQuery),cTrab,.F.,.T.)
			
			U_SYNCLOG(;
				'Agenda : ' +  SubStr(aSyncEnv[4],1,6) + CRLF +;
				'Tarefa : ' +  SubStr(aSyncEnv[4],7,6) + CRLF +;
				cLayout + ' - ' + AllTrim(aSyncEnv[17]) + CRLF +;
				'Query executada com sucesso: ' + CRLF + cQueryBkp)
			
			// Cria arquivo temporario
			
			fErase(Separa(cTempFile,'\',.T.)[3])
			DbCreate(cTempFile,aStru,__LocalDriver)
			DbUseArea(.T.,__LocalDriver,cTempFile,cTempTrab,.T.,.F.)
			
			U_SYNCLOG(;
				'Agenda : ' +  SubStr(aSyncEnv[4],1,6) + CRLF +;
				'Tarefa : ' +  SubStr(aSyncEnv[4],7,6) + CRLF +;
				'Arquivo Temporario criado com sucesso : ' + cTempFile)
			
			// --------------------------------------------------------------
			// Preenchimento do arquivo temporario
			// --------------------------------------------------------------
			
			(cTrab)->(DbGotop())
			
			Do While .Not. (cTrab)->(EOF())
				
				If aScan(aLstCta,SubStr(AllTrim((cTrab)->POS0017),7,7)) == 0 .And. SubStr(SubStr(AllTrim((cTrab)->POS0001),7,7),5,1) # '9'
					
					aAdd(aLstCta,SubStr(AllTrim((cTrab)->POS0017),7,7))
					
					RecLock(cTempTrab,.T.)
					
					(cTempTrab)->POS0001 := StrTran(Str(Year(Date()),4),' ','0') + StrTran(Str(Month(Date()),2),' ','0')
					(cTempTrab)->POS0007 := 'DIXTAL'
					(cTempTrab)->POS0017 := 'DIXTAL' + SubStr(AllTrim((cTrab)->POS0017),7,7)
					(cTempTrab)->POS0057 := (cTrab)->POS0057
					(cTempTrab)->POS0058 := (cTrab)->POS0058
					(cTempTrab)->POS0076 := (cTrab)->POS0076
					(cTempTrab)->POS0084 := SubStr(AllTrim((cTrab)->POS0084),7,7)
					(cTempTrab)->POS0109 := (cTrab)->POS0109
					(cTempTrab)->POS0259 := (cTrab)->POS0259
					(cTempTrab)->POS0267 := If(Empty((cTrab)->CTAVM),'N','S')
					(cTempTrab)->POS0268 := (cTrab)->POS0268
					(cTempTrab)->POS0269 := (cTrab)->POS0269
					
					//------- Verifica a Conta Referencial Sintetica
					If SubStr((cTrab)->POS0270,1,1) $ '125'
						
						aCtRfSint := StrToKArr((cTrab)->POS0270,'.',.T.)
						
						(cTempTrab)->POS0270 := AllTrim((cTempTrab)->POS0270) + If(Len(aCtRfSint) >= 1,aCtRfSint[1] + '.','')
						(cTempTrab)->POS0270 := AllTrim((cTempTrab)->POS0270) + If(Len(aCtRfSint) >= 2,aCtRfSint[2] + '.','')
						(cTempTrab)->POS0270 := AllTrim((cTempTrab)->POS0270) + If(Len(aCtRfSint) >= 3,aCtRfSint[3],'')
						
					ElseIf SubStr((cTrab)->POS0270,1,1) $ '34'
						
						aCtRfSint := StrToKArr((cTrab)->POS0270,'.',.T.)
						
						(cTempTrab)->POS0270 := AllTrim((cTempTrab)->POS0270) + If(Len(aCtRfSint) >= 1,aCtRfSint[1] + '.','')
						(cTempTrab)->POS0270 := AllTrim((cTempTrab)->POS0270) + If(Len(aCtRfSint) >= 2,aCtRfSint[2] + '.','')
						(cTempTrab)->POS0270 := AllTrim((cTempTrab)->POS0270) + If(Len(aCtRfSint) >= 3,aCtRfSint[3] + '.','')
						(cTempTrab)->POS0270 := AllTrim((cTempTrab)->POS0270) + If(Len(aCtRfSint) >= 4,aCtRfSint[4] + '.','')
						(cTempTrab)->POS0270 := AllTrim((cTempTrab)->POS0270) + If(Len(aCtRfSint) >= 5,aCtRfSint[5],'')
						
					End If
					//-----------------------------------------------
					
					(cTempTrab)->POS0295 := SubStr((cTrab)->POS0295,2,1)
					(cTempTrab)->POS0296 := If('/' + (cTrab)->NATUREZA + '/' $ '/01/02/','4','6')
					(cTempTrab)->POS0297 := (cTrab)->POS0297
					
					MsUnLock()
					
					nCount++
					
				End If
				
				(cTrab)->(DbSkip())
				
			End Do
			
			// --------------------------------------------------------------
			
			U_SYNCDAT(cTempTrab,aSyncEnv,nCount)
			
			(cTrab)->(DbCloseArea())
			(cTempTrab)->(DbCloseArea())
			fErase(Separa(cTempFile,'\',.T.)[3])
			
		End IF
		
	Else
		
		U_SYNCLOG(;
			'Agenda : ' +  SubStr(aSyncEnv[4],1,6) + CRLF +;
			'Tarefa : ' +  SubStr(aSyncEnv[4],7,6) + CRLF +;
			'Nao foi possivel montar o ambiente de geracao do layout ' + cLayout)
		
	End IF
	
	RpcClearEnv()
	
Return
